CREATE proc [dbo].[p_AverageDayBlockCount](
	@Date datetime, 
	@DatabaseID bigint) AS  

set nocount on

select
	datepart(hh,a.[CreateDate]) 'iHour',
	count(*) 'CountOfBlocks',  	
	count(distinct a.SessionID) 'CountOfSessions'
into
	#x 
from  	
	blockinghistory a  		
where  
	a.[DatabaseID]=@DatabaseID
	and datediff(day,a.CreateDate,@Date)=0

group by
	datepart(hh,a.[CreateDate])

declare @hours table(
		iHour int,
		iTotalCount int)   
 
declare @iHour int,  		
	@dtCurDate datetime,
	@iTotalCount int 

set @iHour=0  
while @iHour<24
begin  	
	set @dtCurDate=dateadd(hour,@iHour,@Date)  	

	select 
		@iTotalCount=count(*) 
	from 
		blockinghistory a 
	where 
		a.[CreateDate] between @dtCurDate and dateadd(hh,1,@dtCurDate)  
		and a.[DatabaseID]=@DatabaseID

	insert into @hours(
		iHour,
		iTotalCount) values(

		datepart(hh,@dtCurDate),
		@iTotalCount)    	

	set @iHour=@iHour+1  
end   

select   	
	a.iHour,
	dateadd(hh,a.iHour,@Date) 'dtWeekDate',
	case 
		when b.CountOfSessions is null then 0
		else convert(decimal(18,6),isnull(convert(decimal(18,6),b.CountOfBlocks),0)/isnull(b.CountOfSessions,0))
	end 'AvgCountOfAllBlocks'
from   	
	@hours a  	
		left outer join #x b on b.iHour=a.iHour	  
order by   	
	a.iHour 

drop table #x
   




